"""
Case Type   : 协议兼容
Case Name   : 预备语句中多表嵌套查询，含查询交集，查询并集
Create At   : 2024.09.23
Owner       : @zhanghuan96
Description :
    1、创建兼容b库并查询插件是否加载成功
    2、创建用户
    3、caching_sha2认证
    4、查询系统未使用端口
    5、设置协议兼容guc参数
    6、建表
    7、创建预备语句，两个表连接查询
    8、重新给变量b赋值并执行预备语句
    9、创建预备语句，三个表连接查询
    10、创建预备语句
    11、创建预备语句
    12、创建并执行预备语句
    13、创建并执行预备语句
    14、清理环境
Expect      :
    1、成功，插件查询成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、返回表1id1=1和表2id1=1的数据
    8、返回表1id1=1和表2id1=3的数据
    9、返回表1id1=2和表2id1=2的数据及表3id11=2的id11值
    10、返回表1id1=表2id1的值且表1的id1=3
    11、成功
    12、成功
    13、成功
    14、成功
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonMySQL import CommonMySQL
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class CompatibilityTest01(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_pri = CommonSH('PrimaryDbUser')
        self.pri_node = Node('PrimaryDbUser')
        self.my_node = Node('mysql8')
        self.constant = Constant()
        self.db_name = 'db_protocol_0059'
        self.u_name = 'u_protocol_0059'
        self.t_name = 't_protocol_0059'
        self.conf_path = os.path.join(macro.DB_INSTANCE_PATH,
                                      'postgresql.conf')
        self.default_value1 = self.common.show_param('enable_dolphin_proto')
        self.default_value2 = self.common.show_param('dolphin_server_port')
        self.exec_sql1 = f'''drop table if exists {self.t_name};
        create table {self.t_name}(id1 int, id2 int, num int);
        insert into {self.t_name} values (1,1,11), (2,2,21), (3,3,31), \
        (4,4,41), (5,5,51);
        drop table if exists {self.t_name}_01;
        create table {self.t_name}_01(id1 int, id2 int, num int);
        insert into {self.t_name}_01 values (1,2,12), (2,3,22), (3,4,32), \
        (4,5,42), (5,6,52);
        drop table if exists {self.t_name}_02;
        create table {self.t_name}_02(id11 int, id22 int, num int);
        insert into {self.t_name}_02 values (1,13,13), (2,23,23), (3,33,33), \
        (4,43,43), (5,53,53);'''
        self.exec_sql2 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
        prepare p0 as 'select * from {self.t_name} t1,{self.t_name}_01 \
        t2 where t1.id1=? and t2.id1=?';
        execute p0 using @a,@a;'''
        self.exec_sql3 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
        prepare p0 as 'select * from {self.t_name} t1,{self.t_name}_01 \
        t2 where t1.id1=? and t2.id1=?';
        execute p0 using @a,3;'''
        self.exec_sql4 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
        prepare p1 as 'select id11 from {self.t_name} t1,{self.t_name}_01 t2,\
        {self.t_name}_02 t3 where t1.id1=? and t2.id1=? and t3.id11=?';
        execute p1 using 2,@b,2;'''
        self.exec_sql5 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
        prepare p2 as 'select * from {self.t_name} t1,{self.t_name}_01 t2 \
        where t1.id1=t2.id1 and t1.id1=?';
        execute p2 using @c;'''
        self.exec_sql6 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
        prepare p3 as 'select id11 from {self.t_name} t1,{self.t_name}_01 t2,\
        {self.t_name}_02 t3 where t1.id1=t2.id1 and t3.id11=?';
        execute p3 using 4;'''
        self.exec_sql7 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
       prepare p4 as 'select id11 from {self.t_name} t1,{self.t_name}_01 t2,\
       {self.t_name}_02 t3 where t1.id1=t2.id1 and t1.id1=t3.id11 and \
       t3.id11=?';
        execute p4 using 5;'''
        self.exec_sql8 = f'''set dolphin.b_compatibility_mode to on;
        set enable_set_variable_b_format to on;
        set @a =1;
        set @b =2;
        set @c =3;
       prepare p5 as 'select * from {self.t_name} t1,{self.t_name}_01 t2 where \
       t1.id1=t2.id2 and t2.id1=? and t2.id2=?';
       execute p5 (4,5);'''

    def test_create_function(self):
        text = '-----step1:创建兼容b库并查询插件;expect:成功-----'
        self.log.info(text)
        sql_cmd = (f"drop database if exists {self.db_name};"
                   f"create database {self.db_name} dbcompatibility ='B';")
        self.log.info(sql_cmd)
        sql_res = self.sh_pri.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertEqual(1,
                         sql_res.count(self.constant.CREATE_DATABASE_SUCCESS),
                         '兼容B库创建失败')
        sql_cmd1 = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd1)
        sql_res1 = self.sh_pri.execut_db_sql(sql_cmd1, dbname=self.db_name)
        self.log.info(sql_res1)
        self.assertIn('dolphin', sql_res1, '查询失败')

        text = '---step2:创建用户;expect:成功---'
        self.log.info(text)
        sql_cmd2 = (f"drop user if exists {self.u_name};"
                    f"create user {self.u_name} with password "
                    f"'{macro.COMMON_PASSWD}';"
                    f"grant all privileges to {self.u_name};")
        self.log.info(sql_cmd2)
        sql_res2 = self.sh_pri.execut_db_sql(sql_cmd2, dbname=self.db_name)
        self.log.info(sql_res2)
        self.assertIn('CREATE ROLE', sql_res2, '创建失败')
        self.assertIn('ALTER ROLE', sql_res2, '创建失败')

        text = '---step3:caching_sha2认证;expect:成功---'
        self.log.info(text)
        sql_cmd = (f"select set_caching_sha2_password('{self.u_name}', "
                   f"'{macro.COMMON_PASSWD}', '');")
        self.log.info(sql_cmd)
        sql_res = self.sh_pri.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertIn('(1 row)', sql_res, '创建失败')

        text = '----step4:查询系统未使用端口;expect:成功----'
        self.log.info(text)
        port = self.common.get_not_used_port(self.pri_node)
        self.log.info(port)
        self.assertNotEqual(0, port, '执行失败:' + text)

        text = '-step5:设置协议兼容guc参数;expect:成功-'
        self.log.info(text)
        msg = self.sh_pri.execute_gsguc('set',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        'enable_dolphin_proto = on')
        self.log.info(msg)
        self.assertTrue(msg)
        msg = self.sh_pri.execute_gsguc('set',
                                        self.constant.GSGUC_SUCCESS_MSG,
                                        f'dolphin_server_port ={port}')
        self.log.info(msg)
        self.assertTrue(msg)
        sed_cmd = (f"sed -i '$a dolphin.default_database_name={self.db_name}' "
                   f"{self.conf_path}")
        self.log.info(sed_cmd)
        res = self.common.get_sh_result(self.sh_pri.node, sed_cmd)
        self.log.info(res)
        msg = self.sh_pri.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_pri.get_db_cluster_status()
        self.assertTrue("Degraded" in status or "Normal" in status)

        text = '-step6:建表;expect:成功--'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql1, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertNotIn('ERROR', c_res)

        text = '-step7:创建预备语句，两个表连接查询;expect:返回表1id1=1和表2id1=1的数据'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql2, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid1\tid2\tnum\tid1\tid2\tnum\n1\t1\t11'
                      '\t1\t2\t12', c_res)

        text = ('-step8:重新给变量b赋值并执行预备语句;expect:返回表1id1=1和'
                '表2id1=3的数据--')
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql3, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid1\tid2\tnum\tid1\tid2\tnum\n1\t1\t11'
                      '\t3\t4\t32', c_res)

        text = ('-step9:创建预备语句，三个表连接查询;expect:返回表1id1=2和表2id1=2的数据'
                '及表3id11=2的id11值--')
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql4, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid11\n2', c_res)

        text = '-step10:创建预备语句;expect:返回表1id1=表2id1的值且表1的id1=3--'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql5, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid1\tid2\tnum\tid1\tid2\tnum\n3\t3\t31'
                      '\t3\t4\t32', c_res)

        text = '-step11:创建预备语句;expect:成功--'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql6, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid11\n4\n4\n4\n4\n4', c_res)

        text = '-step12:创建预备语句;expect:成功--'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql7, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid11\n5', c_res)

        text = '-step13:创建并执行预备语句;expect:成功--'
        self.log.info(text)
        my_sh = CommonMySQL('mysql8')
        c_res = my_sh.execut_db_sql(sql=self.exec_sql8, dbname=self.db_name,
                                    host=self.pri_node.db_host,
                                    port=port, user=self.u_name,
                                    password=macro.COMMON_PASSWD)
        self.log.info(c_res)
        self.assertIn('\nid1\tid2\tnum\tid1\tid2\tnum\n5\t5\t51'
                      '\t4\t5\t42', c_res)

    def tearDown(self):
        text = '-----step14:清理环境;expect:成功-----'
        self.log.info(text)
        drop_cmd = f"drop database if exists {self.db_name};"
        self.log.info(drop_cmd)
        drop_res = self.sh_pri.execut_db_sql(drop_cmd, dbname='postgres')
        self.log.info(drop_res)
        drop_cmd2 = f"drop user if exists {self.u_name};"
        self.log.info(drop_cmd2)
        drop_res2 = self.sh_pri.execut_db_sql(drop_cmd2)
        self.log.info(drop_res2)

        guc_cmd = self.sh_pri.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'enable_dolphin_proto = '
                                            f'{self.default_value1}')
        self.log.info(guc_cmd)
        self.assertTrue(guc_cmd)
        guc_cmd = self.sh_pri.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f'dolphin_server_port ='
                                            f'{self.default_value2}')
        self.log.info(guc_cmd)
        self.assertTrue(guc_cmd)

        sed_cmd = (f"sed -i '/dolphin.default_database_name={self.db_name}/d' "
                   f"{self.conf_path}")
        self.log.info(sed_cmd)
        res = self.common.get_sh_result(self.sh_pri.node, sed_cmd)
        self.log.info(res)
        msg = self.sh_pri.restart_db_cluster()
        self.log.info(msg)
        status = self.sh_pri.get_db_cluster_status()
        self.log.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status)
        self.assertIn('DROP ROLE', drop_res2, '清理环境失败')
        self.assertEqual(1,
                         drop_res.count(self.constant.DROP_DATABASE_SUCCESS),
                         '清理环境失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
